# _*_ coding:utf-8 _*_
# @File  : migrate_receipt.py
# @Time  : 2021-09-28  13:19
# @Author: zizle

# 迁移仓单数据
import time
import datetime

from db import FAConnection
import pandas as pd


def get_receipt(ts):
    db = FAConnection()
    records = db.query('SELECT id,warehouse_name,variety_en,date,receipt,increase '
                       'FROM delivery_warehouse_receipt WHERE `date`=%s;', [ts])
    return pd.DataFrame(records)


def date_generator(start, end):
    start_date = datetime.datetime.strptime(start, '%Y-%m-%d')
    end_date = datetime.datetime.strptime(end, '%Y-%m-%d')
    while start_date <= end_date:
        yield start_date.strftime('%Y%m%d')
        start_date += datetime.timedelta(days=1)


def start_migrate():
    for d in date_generator('2021-01-02', '2021-09-27'):
        df = get_receipt(d)
        if df.empty:
            continue
        df['receipt'] = df['receipt'].apply(float)
        df['increase'] = df['increase'].apply(float)
        df = df[(df['receipt'] != 0) | (df['increase'] != 0)]
        df['warehouse'] = df['warehouse_name'].apply(lambda x: x.strip())
        df['receipt_ts'] = df['date'].apply(lambda x: int(datetime.datetime.strptime(x, '%Y%m%d').timestamp()))
        df['creator'] = [1 for _ in range(df.shape[0])]
        del df['warehouse_name']
        del df['date']
        del df['id']
        save_receipt(df.to_dict(orient='records'), d)
        time.sleep(0.6)


def save_receipt(save_data, ts):
    # 保存数据
    save_sql = 'INSERT IGNORE INTO dat_futures_daily_receipt (creator,receipt_ts,variety_en,warehouse,receipt,' \
               'increase) ' \
               'VALUES (%(creator)s,%(receipt_ts)s,%(variety_en)s,%(warehouse)s,%(receipt)s,' \
               '%(increase)s);'

    db = FAConnection(conn_name='Save Receipts')
    count, _ = db.insert(save_sql, param=save_data, many=True)
    print(f'保存 {ts} 仓单日报完成,{count}个。')


if __name__ == '__main__':
    start_migrate()
    # db = FAConnection(conn_name='Save Receipts')
    # db.correct_table_auto_increment(table_name='dat_futures_daily_receipt', finish_closed=True)
